package com.study.money;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.study.util.FileUtil;
import org.apache.commons.lang3.StringUtils;

import java.io.File;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * TODO
 *
 * @author wanghuilong
 * @version 1.0
 * @date 2022/2/10 7:50 下午
 */
public class Test3 {

    private static String dataPath = "/Users/wanghuilong/IdeaProjects/lazy/src/main/java/com/study/money/data/";

    //MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/money?useSSL=false&serverTimezone=UTC";

    // 数据库的用户名与密码，需要根据自己的设置
    static final String USER = "root";
    static final String PASS = "123456";

    static Connection conn = null;
    static Statement stmt = null;
    static ResultSet rs = null;

    public static void init() {
        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);

            // 打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            // 执行查询
            System.out.println(" 实例化Statement对象...");
            stmt = conn.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        File file = new File(dataPath);
        init();
        for (File subFile : file.listFiles()) {
            String str = FileUtil.read(subFile.getPath());
            JSONObject jsonObject = JSONObject.parseObject(str);
            JSONArray data = jsonObject.getJSONArray("data");
            //{"nav":"1.0510","fhje":"","code":"003096","tdate":"2019-02-11","fhms":"","rzgl":"1.74","shzt":"开放赎回","sgzt":"限制大额申购","ljjz":"1.1260"}
            /**
             * code varchar(32) NOT NULL COMMENT '编号',
             * fund_type varchar(32) NOT NULL COMMENT '类型',
             * net_worth decimal(10,4) NOT NULL COMMENT '净值',
             * grow_rate decimal(10,4) NOT NULL COMMENT '日增长率(%)',
             * tdate date NOT NULL COMMENT '日期',
             * status int(10) default 0 COMMENT '状态',
             * comment varchar(256) default '' COMMENT '备注',
             */
            saveData(data);
        }
    }

    public static void saveData(JSONArray data) {
        if (null == data || data.size() == 0) {
            return;
        }
        String sql = "insert into net_worth(code,fund_type,net_worth,grow_rate,tdate,comment) values";
        StringBuilder sb = new StringBuilder(sql);
        List<String> valueList = new ArrayList<>(100);
        for (int i = 0; i < data.size(); i++) {
            JSONObject dataObject = data.getJSONObject(i);
            try {
                String code = dataObject.getString("code");
                String nav = dataObject.getString("nav");
                String tdate = dataObject.getString("tdate");
                String rzgl = dataObject.getString("rzgl");
                if (StringUtils.isBlank(rzgl)) {
                    rzgl = "0";
                }
                String shzt = dataObject.getString("shzt");
                String sgzt = dataObject.getString("sgzt");
                BigDecimal netWorth = new BigDecimal(nav);
                BigDecimal growRateValue = new BigDecimal(rzgl);
                String comment = shzt + ";" + sgzt;
                String valueStr = new StringBuilder().append("('").append(code).append("','").append(FundTypeEnum.FUND.getDesc()).append("',")
                        .append(netWorth).append(",").append(growRateValue).append(",'").append(tdate).append("','")
                        .append(comment).append("')").toString();
                valueList.add(valueStr);
                if (valueList.size() == 100) {
                    sb.append(StringUtils.join(valueList, ","));
                    try {
                        stmt.execute(sb.toString());
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    sb = new StringBuilder(sql);
                    valueList = new ArrayList<>(100);
                }
            } catch (Exception e) {
                System.out.println(dataObject.toJSONString());
            }
        }
        if (valueList.size() > 0) {
            sb.append(StringUtils.join(valueList, ","));
            try {
                stmt.execute(sb.toString());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public static void checkAndSaveFund(String code, String fundType, String name) {
        String querySql = new StringBuilder("select id from fund where code='").append(code).append("' and fund_type='")
                .append(fundType).append("' and name='").append(name).append("'").toString();
        boolean has = false;
        try {
            ResultSet resultSet = stmt.executeQuery(querySql);
            if (resultSet.next()) {
                long id = resultSet.getLong("id");
                if (id > 0L) {
                    has = true;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (Boolean.TRUE.equals(has)) {
            return;
        }
        String addSql = new StringBuilder("insert into fund(code,fund_type,name) values('").append(code).append("','")
                .append(fundType).append("','").append(name).append("')")
                .toString();
        try {
            stmt.executeUpdate(addSql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}
